﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;

namespace OALPTool
{
    public partial class FrmOLAPTool : Form
    {
        #region 自定义变量
        private int ret;
        private string strMsg;
        private DataView dvExcel = null;
        #endregion

        #region 初始化

        public FrmOLAPTool()
        {
            InitializeComponent();
        }

        private void FrmOLAPTool_Load(object sender, EventArgs e)
        {
            ret = InitForm(out strMsg);
            if (ret < 0)
            {
                TSysPublic.ShowErrMsg(strMsg);
                return;
            }
        }

        public int InitForm(out string strMsg)
        {
            strMsg = "";

            ret = InitFormData(out strMsg);
            if (ret < 0)
            {
                TSysPublic.ShowErrMsg(strMsg);
                return ret;
            }
            DisableControl();
            return 0;
        }

        private int InitFormData(out string strMsg)
        {
            strMsg = "";
            try
            {


                //dgvTmplate.TopRow.Cells[1].Text = "入库";
                //dgvTmplate.TopRow.Cells[1].ColSpan = 2;

                //this.dgvTmplate.ColumnHeadersHeight = 40;
                //this.dgvTmplate.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing;
                //this.dgvTmplate.AddSpanHeader(8, 2, "XXXX");
                //this.dgvTmplate.AddSpanHeader(0, 1, "级别 04");
                //this.dgvTmplate.AddSpanHeader(1, 1, "级别 05");
                //this.dgvTmplate.AddSpanHeader(2, 1, "级别 06");
                //this.dgvTmplate.AddSpanHeader(3, 1, "级别 07");
                //this.dgvTmplate.AddSpanHeader(4, 1, "级别 08");
                //this.dgvTmplate.AddSpanHeader(5, 1, "级别 09");
                //this.dgvTmplate.AddSpanHeader(6, 1, "级别 02");
                //this.dgvTmplate.AddSpanHeader(7, 1, "级别 03");


            }
            catch (Exception ex)
            {
                strMsg = "函数InitFormData异常：" + ex.Message.ToString();
                return -1997;

            }
            return 0;
        }

        private void DisableControl()
        {

        }

        #endregion

        #region 界面事件函数

        /// <summary>
        /// 工具栏
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void tb_Menu_ButtonClick(object sender, ToolBarButtonClickEventArgs e)
        {
            if (e.Button.Text == "退出")
            {
                this.Close();
            }
            else if (e.Button.Text == "导出")
            {
                dgvTmplate.ExportExcel();
                //ret = OutToExcel(this.dgvTmplate, out strMsg);
                //if (ret < 0)
                //{
                //    TSysPublic.ShowMsg(strMsg);
                //    return;
                //}

            }
            else if (e.Button.Text == "导入")
            {

            }
        }

        /// <summary>
        /// 选择文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSelFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog file = new OpenFileDialog();
            file.Title = "请选择Excel文件";
            file.Filter = "Excel文件(*.xls)|*.xls";
            if (file.ShowDialog() == DialogResult.OK)
            {
                this.tbFilePath.Text = file.FileName;
                try
                {
                    ArrayList sheets = ExcelSheetName(file.FileName.Trim());
                    this.cmbSheet.Items.Clear();
                    for (int i = 0; i < sheets.Count; i++)
                    {
                        this.cmbSheet.Items.Add(sheets[i].ToString().Trim());
                    }
                    if (this.cmbSheet.Items.Count > 0)
                    {
                        this.cmbSheet.SelectedIndex = 0;
                    }
                }
                catch (Exception ex)
                {
                    TSysPublic.ShowErrMsg(ex.Message);
                }
            }
            else
            {
                return;
            }
        }

        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnInput_Click(object sender, EventArgs e)
        {
            if (dvExcel != null)
            {
                dvExcel = null;
                dgvTmplate.DataSource = null;
            }
            if (this.tbFilePath.Text == "" || this.cmbSheet.Text == "")
            {
                TSysPublic.ShowMsg("请输入需要导入excel的路径和sheet表名！");
                return;
            }
            //读取Excel数据
            string sExcelFile = this.tbFilePath.Text.Trim();
            string sConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + sExcelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
            OleDbConnection connection = new OleDbConnection(sConnectionString);
            string strLinkSql = "Select * from [" + this.cmbSheet.Text.Trim() + "$]";

            OleDbDataAdapter adp = new OleDbDataAdapter(strLinkSql, connection);

            DataSet ds = new DataSet();
            try
            {
                adp.Fill(ds, "Book1");
            }
            catch (Exception ex)
            {
                TSysPublic.ShowMsg("读取表底示数数据不成功：" + ex.Message);

                return;
            }
            if (ds == null || ds.Tables.Count < 0 || ds.Tables[0].Rows.Count < 0)
            {
                TSysPublic.ShowMsg("读取表底示数数据为空！");

                return;
            }

            dvExcel = new DataView(ds.Tables[0]);

            //设置Excel格式
            ret = FormatExcelData();
            if (ret < 0)
            {
                TSysPublic.ShowMsg("设置格式失败！");
                return;
            }

            //dgvTmplate.AutoGenerateColumns = false;
            dgvTmplate.DataSource = dvExcel;

        }

        #endregion

        #region 辅助函数
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="DGV"></param>
        /// <param name="strErr"></param>
        /// <returns></returns>
        private int OutToExcel(DataGridView DGV, out string strErr)
        {
            strErr = "";
            ArrayList argsExcel = new ArrayList();
            object oprret = null;
            if (DGV.RowCount <= 0)
            {
                strErr = "没有可导出的数据！";
                return -1997;
            }

            ArrayList alFormat = new ArrayList();
            foreach (DataGridViewColumn dgvc in DGV.Columns)
            {
                alFormat.Add(dgvc.DefaultCellStyle.Format);
                dgvc.DefaultCellStyle.Format = "";
            }

            Cursor.Current = Cursors.WaitCursor;
            new DataGridViewToExcel().OALP_DataGridViewToExcel(DGV, null);
            Cursor.Current = Cursors.Default;
            if (ret < 0)
            {
                strErr = "导出出错!";
                return ret;
            }

            for (int i = 0; i < alFormat.Count; i++)
                DGV.Columns[i].DefaultCellStyle.Format = alFormat[i].ToString();

            return 0;
        }

        /// <summary>
        /// Excel连接串
        /// </summary>
        /// <param name="strFilePath"></param>
        /// <returns></returns>
        internal string Con2Excel(string strFilePath)
        {
            return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties=\"Excel 8.0;\"";
        }

        /// <summary>
        /// 获得Excel中的所有sheetname
        /// </summary>
        /// <param name="strFileName"></param>
        /// <returns></returns>
        internal ArrayList ExcelSheetName(string strFilePath)
        {
            ArrayList al = new ArrayList();
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(Con2Excel(strFilePath));
            conn.Open();
            DataTable sheetNames = conn.GetOleDbSchemaTable
                (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            conn.Close();
            foreach (DataRow dr in sheetNames.Rows)
            {
                al.Add(dr[2].ToString().Replace("$", "").Replace("'", ""));
            }
            return al;
        }

        /// <summary>
        /// 设置读取格式
        /// </summary>
        /// <returns></returns>
        private int FormatExcelData()
        {
            try
            {
                //------设置列表二维列头--------------
                //this.dgvTmplate.ColumnDeep = 2;
                //this.dgvTmplate.ColumnHeadersHeight = 60;
                //------设置列表二维列头--------------

                ArrayList colList = new ArrayList();
                colList.Add("区域");
                colList.Add("城市");
                colList.Add("级别");
                colList.Add("销售类别");
                colList.Add("客户名称");
                colList.Add("店铺名称");
                colList.Add("费用科目");
                colList.Add("科目明细");
                colList.Add("201214纳品Val");
                colList.Add("201214实绩");
                colList.Add("201214纳品实绩费率");
                colList.Add("201301纳品Val");
                colList.Add("201301实绩");
                colList.Add("201301纳品实绩费率");
                colList.Add("201302纳品Val");
                colList.Add("201302实绩");
                colList.Add("201302纳品实绩费率");
                colList.Add("201303单位确认纳品金额");
                colList.Add("201303预算金额");
                colList.Add("201303纳品计划费率");
                colList.Add("201304单位确认纳品金额");
                colList.Add("201304预算金额");
                colList.Add("201304纳品计划费率");
                colList.Add("销售金额");
                colList.Add("费用金额");
                colList.Add("费率");
                colList.Add("原因说明");
                colList.Add("解决方法");

                foreach (DataColumn dcHeader in dvExcel.Table.Columns)
                {
                    if (!colList.Contains(dcHeader.ColumnName))
                    {
                        TSysPublic.ShowMsg("列 " + dcHeader.ColumnName + " 列头名称不合规范，请检查！");
                        return -1;
                    }
                }

                dvExcel.Table.Columns["区域"].ColumnName = "Zone"; // 级别04-区域 
                dvExcel.Table.Columns["城市"].ColumnName = "City";//级别05-城市
                dvExcel.Table.Columns["级别"].ColumnName = "SellLevel";//级别06-级别 一级分销商(没有级别8 级别9) 直供企业（级别7为零售、网购 有级别8 级别9）
                dvExcel.Table.Columns["销售类别"].ColumnName = "SellKind";//级别07-销售类别 (零售、网购)
                dvExcel.Table.Columns["客户名称"].ColumnName = "CustomerName";//级别08-客户名称
                dvExcel.Table.Columns["店铺名称"].ColumnName = "ShopName";//级别09
                dvExcel.Table.Columns["费用科目"].ColumnName = "AsName";//级别02-费用科目
                dvExcel.Table.Columns["科目明细"].ColumnName = "AsItemName";// 级别03

                dvExcel.Table.Columns["201214纳品Val"].ColumnName = "SellAmount201214";
                dvExcel.Table.Columns["201214实绩"].ColumnName = "ExpenseAmount201214";
                dvExcel.Table.Columns["201214纳品实绩费率"].ColumnName = "Rates201214";

                dvExcel.Table.Columns["201301纳品Val"].ColumnName = "SellAmount201301";
                dvExcel.Table.Columns["201301实绩"].ColumnName = "ExpenseAmount201301";
                dvExcel.Table.Columns["201301纳品实绩费率"].ColumnName = "Rates201301";

                dvExcel.Table.Columns["201302纳品Val"].ColumnName = "SellAmount201302";
                dvExcel.Table.Columns["201302实绩"].ColumnName = "ExpenseAmount201302";
                dvExcel.Table.Columns["201302纳品实绩费率"].ColumnName = "Rates201302";

                dvExcel.Table.Columns["201303单位确认纳品金额"].ColumnName = "SellAmount201303";
                dvExcel.Table.Columns["201303预算金额"].ColumnName = "ExpenseAmount201303";
                dvExcel.Table.Columns["201303纳品计划费率"].ColumnName = "Rates201303";

                dvExcel.Table.Columns["201304单位确认纳品金额"].ColumnName = "SellAmount201304";
                dvExcel.Table.Columns["201304预算金额"].ColumnName = "ExpenseAmount201304";
                dvExcel.Table.Columns["201304纳品计划费率"].ColumnName = "Rates201304";

                dvExcel.Table.Columns["销售金额"].ColumnName = "SellAmount";//=SUM(L21,O21,R21,U21)
                dvExcel.Table.Columns["费用金额"].ColumnName = "ExpenseAmount";//=SUM(M21,P21,S21,V21)
                dvExcel.Table.Columns["费率"].ColumnName = "Rates";//=费用金额/销售金额
                dvExcel.Table.Columns["原因说明"].ColumnName = "Brief";//对当月预算进行说明
                dvExcel.Table.Columns["解决方法"].ColumnName = "Solution";//


                if (!dvExcel.Table.Columns.Contains("ListNo"))
                {
                    dvExcel.Table.Columns.Add("ListNo", typeof(int));
                }

                if (!dvExcel.Table.Columns.Contains("SumFlag"))
                {
                    dvExcel.Table.Columns.Add("SumFlag", typeof(string));
                }

                if (!dvExcel.Table.Columns.Contains("Formula"))
                {
                    dvExcel.Table.Columns.Add("Formula", typeof(string));
                }
                if (!dvExcel.Table.Columns.Contains("CNFlag"))
                {
                    dvExcel.Table.Columns.Add("CNFlag", typeof(string));
                }
                int ListNo = 3;
                string SellLevel = "";//级别06-级别 一级分销商(没有级别8 级别9) 直供企业（级别7为零售、网购 有级别8 级别9）
                string SellKind = "";//级别07-销售类别 (零售、网购)
                string CustomerName = "";//级别08-客户名称
                string ShopName = "";////级别09-店铺名称***
                foreach (DataRowView drExcel in dvExcel)
                {
                    drExcel["ListNo"] = ListNo++;
                    SellLevel = drExcel["SellLevel"].ToString().Trim();
                    if (SellLevel.Contains("直供企业"))
                    {
                        string ShopNameTemp = drExcel["ShopName"].ToString().Trim();
                        if (!string.IsNullOrEmpty(ShopNameTemp))
                        {
                            if (!ShopNameTemp.Contains("汇总"))
                            {
                                CustomerName = drExcel["CustomerName"].ToString().Trim();
                                ShopName = ShopNameTemp;
                                drExcel["CNFlag"] = "0";
                                drExcel["ExpenseAmount"] = TMath.ConvertToDecimal(drExcel["ExpenseAmount201301"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201302"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201303"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201304"]);
                            }
                            else
                            {
                                drExcel["CustomerName"] = CustomerName;
                                ShopName = "";
                                drExcel["CNFlag"] = "1";
                                drExcel["SellAmount"] = TMath.ConvertToDecimal(drExcel["SellAmount201301"]) + TMath.ConvertToDecimal(drExcel["SellAmount201302"]) + TMath.ConvertToDecimal(drExcel["SellAmount201303"]) + TMath.ConvertToDecimal(drExcel["SellAmount201304"]);
                                drExcel["ExpenseAmount"] = TMath.ConvertToDecimal(drExcel["ExpenseAmount201301"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201302"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201303"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201304"]);
                            }
                        }
                        else
                        {
                            drExcel["CustomerName"] = CustomerName;
                            drExcel["ShopName"] = ShopName;
                            drExcel["CNFlag"] = "0";
                            drExcel["ExpenseAmount"] = TMath.ConvertToDecimal(drExcel["ExpenseAmount201301"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201302"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201303"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201304"]);
                        }
                    }
                    else if (SellLevel.Contains("一级分销商"))
                    {
                        string SellKindTemp = drExcel["SellKind"].ToString().Trim();

                        if (!string.IsNullOrEmpty(SellKindTemp))
                        {
                            if (!SellKindTemp.Contains("汇总"))
                            {
                                SellKind = SellKindTemp;
                                drExcel["CNFlag"] = "0";
                                drExcel["ExpenseAmount"] = TMath.ConvertToDecimal(drExcel["ExpenseAmount201301"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201302"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201303"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201304"]);
                            }
                            else
                            {
                                SellKind = "";
                                drExcel["CNFlag"] = "1";
                                drExcel["SellAmount"] = TMath.ConvertToDecimal(drExcel["SellAmount201301"]) + TMath.ConvertToDecimal(drExcel["SellAmount201302"]) + TMath.ConvertToDecimal(drExcel["SellAmount201303"]) + TMath.ConvertToDecimal(drExcel["SellAmount201304"]);
                                drExcel["ExpenseAmount"] = TMath.ConvertToDecimal(drExcel["ExpenseAmount201301"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201302"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201303"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201304"]);
                            }
                        }
                        else
                        {
                            drExcel["SellKind"] = SellKind;
                            drExcel["CNFlag"] = "0";
                            drExcel["ExpenseAmount"] = TMath.ConvertToDecimal(drExcel["ExpenseAmount201301"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201302"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201303"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201304"]);
                        }
                    }
                }
                dvExcel.Sort = "ListNo";
                dvExcel.Table.AcceptChanges();
            }
            catch (Exception ex)
            {
                TSysPublic.ShowMsg("导入数据字段有问题：" + ex.Message);
                return -1;
            }
            return 0;
        }

        /// <summary>
        /// 计算费率
        /// </summary>
        /// <returns></returns>
        private int ComputeExcelDate()
        {
            try
            {

                string strWhere = "CNFlag='1'";
                DataRow[] drrHZ = dvExcel.Table.Select(strWhere);



                foreach (DataRow drHZ in drrHZ)
                {
                    int ListNoHz = TMath.ConvertToInt(drHZ["ListNo"]);
                    decimal SellAmount = TMath.ConvertToDecimal(drHZ["SellAmount"]);
                    string SellLevelHZ = drHZ["SellLevel"].ToString().Trim();//级别06-级别 一级分销商(没有级别8 级别9) 直供企业（级别7为零售、网购 有级别8 级别9）
                    string NameHZ = "";
                    if (SellLevelHZ.Contains("直供企业"))
                    {
                        NameHZ = drHZ["ShopName"].ToString().Trim();
                    }
                    else if (SellLevelHZ.Contains("一级分销商"))
                    {
                        NameHZ = drHZ["SellKind"].ToString().Trim();
                    }

                    foreach (DataRowView drExcel in dvExcel)
                    {
                        string SellLevel = drExcel["SellLevel"].ToString().Trim();//级别06-级别 一级分销商(没有级别8 级别9) 直供企业（级别7为零售、网购 有级别8 级别9）
                        string SumFlag = drExcel["SumFlag"].ToString().Trim();
                        int ListNoExcel = TMath.ConvertToInt(drExcel["ListNo"]);
                        string CName = "";
                        if (SellLevel.Contains("直供企业"))
                        {
                            CName = drExcel["ShopName"].ToString().Trim();

                            if (!CName.Contains("汇总"))
                            {
                                CName = drExcel["ShopName"].ToString().Trim() + " 汇总";

                            }

                        }
                        else if (SellLevel.Contains("一级分销商"))
                        {
                            CName = drExcel["SellKind"].ToString().Trim();

                            if (!CName.Contains("汇总"))
                            {
                                CName = drExcel["SellKind"].ToString().Trim() + " 汇总";

                            }
                        }


                        if (NameHZ == CName && string.IsNullOrEmpty(SumFlag))
                        {
                            if (SellAmount > TMath.P_FINANCE_ZERO_2)
                            {
                                decimal ExpenseAmount = TMath.ConvertToDecimal(drExcel["ExpenseAmount"]);
                                drExcel["Rates"] = (ExpenseAmount / SellAmount);
                                drExcel["SumFlag"] = (ExpenseAmount / SellAmount).ToString("P");
                                drExcel["Formula"] = "=Y" + ListNoExcel + "/$X$" + ListNoHz;

                            }
                            else
                            {
                                drExcel["Rates"] = 0m;
                                drExcel["SumFlag"] = "0.0%";
                            }
                        }
                    }
                    dvExcel.Table.AcceptChanges();
                }
            }
            catch (Exception ex)
            {
                TSysPublic.ShowMsg("导入数据字段有问题：" + ex.Message);
                return -1;
            }

            return 0;
        }

        /// <summary>
        /// 设置染色
        /// </summary>
        /// <returns></returns>
        private int ColorExcelData()
        {

            //审核标准：									
            //（一级客户，代理商及直供企业）。									
            //① 生理   代理商 25%     直供 30%  以上									
            //② 婴儿   代理商 20%     直供 30%  以上 （均含 20%、25%、30%）									
            //费率用红色、黄色标出。红色为高（高于以上标准或者有费用没有销售的），黄色为低（低于10%）。									

            //费用明细审核标准:									
            //KA企业:									
            //陈列费、堆卖费、店头建设费单项费率：6%以上（含6%）									
            //促销小姐：10%以上（含10%）									
            //同期比：单项费率：高于同期2%				当月预算和同期实绩进行对比。					
            //代理商：									
            //同期比：单项费率：高于同期2%									
            return 0;
        }
        #endregion

        private void butComputeExcelDate_Click(object sender, EventArgs e)
        {
            ret = ComputeExcelDate();
            if (ret < 0)
            {
                TSysPublic.ShowMsg("计算费率异常！");
                return;
            }
        }
    }
}
